﻿Imports Microsoft.VisualBasic
Imports System.Data

<System.ComponentModel.DataObjectAttribute(True)> _
Public Class BLL_AddEnlistment_Course

    Public Sub New()
        ' 
        ' TODO: 在此加入建構函式的程式碼 
        ' 
    End Sub

    'SELECT 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.[Select])> _
    Public Function BLL_Select(ByVal EnlistID As Integer) As DataTable

        Dim sql As New MyStringBuilder
        Dim dat As New clsData

        sql.AppendFormat("SELECT A.* ,B.[Text] as CourseTypeName ")
        sql.AppendFormat("FROM [EnlistTrainingCourse] A ")
        sql.AppendFormat("left join CodeMapping  B on A.CourseType=B.[Value] and B.TableName='CleanPlan' and B.ColumnName='ItemType' and B.Active='Y' ")
        sql.AppendFormat("WHERE (A.[EnlistID] ={0}) ", EnlistID)
        sql.AppendFormat("ORDER BY A.[EnlistID] ")
        Return dat.GetData(sql)

    End Function

    'Insert 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Insert)> _
    Public Function BLL_Insert(ByVal EnlistID As Integer, ByVal CourseName As String, ByVal TrainingHour As Integer, ByVal CourseType As String, ByVal StartDate As DateTime, ByVal EndDate As DateTime, ByVal CreateBy As String) As Integer

        Dim dat As New clsData
        Dim sql As New MyStringBuilder

        Using scope As New Transactions.TransactionScope

            sql.AppendFormat("INSERT INTO [EnlistTrainingCourse] ([EnlistID], [CourseName], [TrainingHour], [CourseType], [StartDate], [EndDate], [CreateDate], [CreateBy]) ")
            sql.AppendFormat("SELECT EnlistID={0} ", EnlistID)
            sql.AppendFormat(", '{0}'", CourseName)
            sql.AppendFormat(", {0}", TrainingHour)
            sql.AppendFormat(", '{0}'", CourseType)
            sql.AppendFormat(", '{0:yyyy/MM/dd}'", StartDate)
            sql.AppendFormat(", '{0:yyyy/MM/dd}'", EndDate)
            sql.AppendFormat(", getdate()")
            sql.AppendFormat(", '{0}'; ", CreateBy)

            dat.AccData(sql)
            scope.Complete()

            Return 1

        End Using

    End Function

    'Update 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Update)> _
    Public Function BLL_Update(ByVal CourseName As String, ByVal TrainingHour As Integer, ByVal CourseType As String, ByVal StartDate As DateTime, ByVal EndDate As DateTime, ByVal UpdateBy As String, ByVal original_CourseID As Integer) As Integer

        Dim dat As New clsData
        Dim sql As New MyStringBuilder

        Using scope As New Transactions.TransactionScope

            sql.AppendFormat("UPDATE EnlistTrainingCourse ")
            sql.AppendFormat("SET CourseName='{0}'", CourseName)
            sql.AppendFormat(",TrainingHour={0}", TrainingHour)
            sql.AppendFormat(",CourseType='{0}'", CourseType)
            sql.AppendFormat(",StartDate='{0:yyyy/MM/dd}'", StartDate)
            sql.AppendFormat(",EndDate='{0:yyyy/MM/dd}'", EndDate)
            sql.AppendFormat(",UpdateDate=getdate()")
            sql.AppendFormat(",UpdateBy='{0}'", UpdateBy)
            sql.AppendFormat("WHERE CourseID={0}; ", original_CourseID)

            dat.AccData(sql)

            scope.Complete()

            Return 1

        End Using

    End Function

    'Delete 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Delete)> _
    Public Sub BLL_Delete(ByVal original_CourseID As Integer)

        Dim dat As New clsData
        Dim sql As New MyStringBuilder

        Using scope As New Transactions.TransactionScope

            sql.Length = 0
            sql.AppendFormat("DELETE [EnlistTrainingCourse] WHERE [CourseID] = {0}", original_CourseID)
            dat.AccData(sql)

            scope.Complete()

        End Using

    End Sub

End Class
